package cn.chiship.sdk.core.util.excel.core;

import cn.chiship.sdk.core.base.BaseResult;
import cn.chiship.sdk.core.enums.FileExtEnum;
import cn.chiship.sdk.core.exception.custom.BusinessException;
import cn.chiship.sdk.core.util.DateUtils;
import cn.chiship.sdk.core.util.PrintUtil;
import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 支持2007以上版本，Excel 2007 OOXML (.xlsx)
 *
 * @author lijian
 */
public class ExcelXSSFService implements ExcelService {

	private static XSSFWorkbook wb;

	private static XSSFCellStyle cellStyle;

	public ExcelXSSFService() {
		PrintUtil.console("初始化加载xlsx引擎");
		wb = new XSSFWorkbook();
	}

	@Override
	public void load(String file) {
		try {
			wb = new XSSFWorkbook(new FileInputStream(file));
		}
		catch (Exception exception) {
			throw new BusinessException("xlsx引擎载入excel文件出错，" + exception.getMessage());
		}
	}

	@Override
	public void load(InputStream is) {
		try {
			wb = new XSSFWorkbook(is);
		}
		catch (Exception exception) {
			throw new BusinessException("xlsx引擎载入excel文件出错，" + exception.getMessage());
		}
	}

	@Override
	public InputStream getInputStream() throws IOException {
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		wb.write(out);
		byte[] bytes = out.toByteArray();
		return new ByteArrayInputStream(bytes);
	}

	@Override
	public BaseResult readExcel(Integer sheetIndex, Integer firstHeaderRow, Integer firstDataRow,
			Map<String, String> headerTitleMap) {
		sheetIndex = sheetIndex - 1;
		Sheet sheet = wb.getSheetAt(sheetIndex);
		if (sheet == null) {
			return BaseResult.error("Sheet【" + sheetIndex + "】表格不存在！");
		}
		return readExcel(sheet.getSheetName(), firstHeaderRow, firstDataRow, headerTitleMap);
	}

	@Override
	public BaseResult readExcel(String sheetName, Integer firstHeaderRow, Integer firstDataRow,
			Map<String, String> headerTitleMap) {
		if (firstHeaderRow < 1) {
			return BaseResult.error("表头[firstHeaderRow]下标不能小于1");
		}
		if (firstDataRow < 1) {
			return BaseResult.error("数据[firstDataRow]下标不能小于1");
		}
		firstHeaderRow = firstHeaderRow - 1;
		firstDataRow = firstDataRow - 1;
		XSSFSheet sheet = wb.getSheet(sheetName);
		if (sheet == null) {
			return BaseResult.error("Sheet【" + sheetName + "】表格不存在！");
		}
		boolean flag = judgmentHeader(sheet.getRow(firstHeaderRow), headerTitleMap);
		if (flag) {
			XSSFRow row;
			int rowLength = sheet.getPhysicalNumberOfRows();
			List<Map<String, String>> dataList = new ArrayList<>();
			for (Integer rowIndex = 0; rowIndex < rowLength - firstDataRow; rowIndex++) {
				row = sheet.getRow(rowIndex + firstDataRow);
				Map<String, String> dataMap = new HashMap<>(2);
				Integer count = 0;
				for (Map.Entry<String, String> entry : headerTitleMap.entrySet()) {
					dataMap.put(entry.getKey(), getCellValue(row, count++));
				}
				dataList.add(dataMap);
			}
			return BaseResult.ok(dataList);
		}
		else {
			StringBuilder builder = new StringBuilder();
			builder.append(sheetName + "第" + (firstHeaderRow + 1) + "行标题格式只允许");
			for (Map.Entry<String, String> entry : headerTitleMap.entrySet()) {
				builder.append("[" + entry.getValue() + "] ");
			}
			builder.append("格式");
			return BaseResult.error(builder.toString());
		}
	}

	@Override
	public ByteArrayInputStream getWriteExcelByteArrayInputStream(String sheetName, String sheetTitle,
			List<String> headerNames, List<List<String>> values) {
		return new ByteArrayInputStream(getWriteExcelBytes(sheetName, sheetTitle, headerNames, values));
	}

	@Override
	public byte[] getWriteExcelBytes(String sheetName, String sheetTitle, List<String> headerNames,
			List<List<String>> values) {
		try {
			writeExcel(wb, sheetName, sheetTitle, headerNames, values, 1);
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			wb.write(os);
			return os.toByteArray();
		}
		catch (Exception e) {
			throw new BusinessException("xlsx文件导出错误[" + e.getLocalizedMessage() + "]");
		}
	}

	@Override
	public BaseResult writeExcel(String filePath, String fileName, String sheetName, String sheetTitle,
			List<String> headerNames, List<List<String>> values) {
		try {
			FileUtils.forceMkdir(new File(filePath));
			FileOutputStream os = new FileOutputStream(filePath + "/" + fileName + FileExtEnum.FILE_EXT_XLSX.getName());
			writeExcel(os, sheetName, sheetTitle, headerNames, values);
			return BaseResult.ok(null);
		}
		catch (Exception e) {
			return BaseResult.error("xlsx文件导出错误[" + e.getLocalizedMessage() + "]");
		}
	}

	@Override
	public void writeExcel(OutputStream os, String sheetName, String sheetTitle, List<String> headerNames,
			List<List<String>> values) {
		try {
			writeExcel(wb, sheetName, sheetTitle, headerNames, values, 1);
			wb.write(os);
			os.flush();
			os.close();
		}
		catch (Exception e) {
			throw new BusinessException("xlsx文件导出错误[" + e.getLocalizedMessage() + "]");
		}
	}

	@Override
	public void writeExcel(String sheetName, String sheetTitle, Integer sheetNum, List<String> headerNames,
			List<List<String>> values) {
		writeExcel(wb, sheetName, sheetTitle, headerNames, values, sheetNum);
	}

	@Override
	public void writeAndClose(OutputStream os) {
		try {
			wb.write(os);
			os.flush();
			os.close();
		}
		catch (Exception e) {
			throw new BusinessException("xlsx文件导出错误[" + e.getLocalizedMessage() + "]");
		}
	}

	public static void writeExcel(XSSFWorkbook wb, String sheetName, String sheetTitle, List<String> headerNames,
			List<List<String>> values, Integer sheetNum) {
		if (headerNames.isEmpty()) {
			throw new BusinessException("表头不能为空!");
		}
		sheetNum = sheetNum - 1;
		// 创建表格
		XSSFSheet sheet = wb.createSheet();
		// 默认宽度
		sheet.setDefaultRowHeightInPoints(13);
		wb.setSheetName(sheetNum, sheetName);

		XSSFRow row = null;

		/**
		 * 第一行标题
		 */
		int rows = 0;
		row = sheet.createRow(rows);
		setRowHeight(row, 30);
		XSSFCell cell = row.createCell(0);
		setCellStyleWithValue(wb, cell, 16, sheetTitle);
		setMergedRegion(sheet, 0, 0, 0, headerNames.size() - 1);

		/**
		 * 第二行制表信息
		 */
		rows = 1;
		row = sheet.createRow(rows);
		setRowHeight(row);
		cell = row.createCell(0);
		setCellStyleWithValue(wb, cell, createCellStyle(wb, HorizontalAlignment.RIGHT), "制表时间:" + DateUtils.getDate());
		setMergedRegion(sheet, 1, 1, 0, headerNames.size() - 1);

		/**
		 * 表头
		 */
		rows = 2;
		row = sheet.createRow(rows);
		setRowHeight(row);
		for (Integer i = 0; i < headerNames.size(); i++) {
			cell = row.createCell(i);
			setCellStyleWithValue(wb, cell, headerNames.get(i));
		}
		/**
		 * 数据
		 */
		for (int i = 0; i < values.size(); i++) {
			row = sheet.createRow(i + 3);
			setRowHeight(row);
			XSSFCellStyle dataCellStyle = createCellStyle(wb,
					"序号".equals(headerNames.get(0)) ? HorizontalAlignment.CENTER : HorizontalAlignment.LEFT);
			List<String> colValue = values.get(i);
			for (Integer j = 0; j < colValue.size(); j++) {
				cell = row.createCell(j);
				setCellStyleWithValue(wb, cell, dataCellStyle, colValue.get(j));

			}
		}
		sheet.setDefaultRowHeight((short) (20 * 20));
		for (int i = 0; i <= row.getLastCellNum(); i++) {
			sheet.autoSizeColumn(i);
		}
	}

	@Override
	public List<String> getSheetNames() {
		List<String> sheetNames = new ArrayList<>();
		Integer sheetNumber = wb.getNumberOfSheets();
		for (Integer i = 0; i < sheetNumber; i++) {
			sheetNames.add(wb.getSheetAt(i).getSheetName());
		}
		return sheetNames;
	}

	public static String getCellValue(XSSFRow row, int cellNum) {
		XSSFCell cell = row.getCell(cellNum);
		try {
			if (DateUtil.isCellDateFormatted(cell)) {
				double d = cell.getNumericCellValue();
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				return sdf.format(DateUtil.getJavaDate(d));
			}
			else {

				cell.setCellType(CellType.STRING);
				if (cell.getCellType() == CellType.STRING) {
					return (cell.getStringCellValue());
				}
				else if (cell.getCellType() == CellType.NUMERIC) {

					return String.valueOf((int) cell.getNumericCellValue());
				}
			}
		}
		catch (IllegalStateException e) {
			cell.setCellType(CellType.STRING);
			if (cell.getCellType() == CellType.STRING) {
				return (cell.getStringCellValue());
			}
			else if (cell.getCellType() == CellType.NUMERIC) {
				return String.valueOf((int) cell.getNumericCellValue());
			}
		}
		catch (NullPointerException e) {
			return "";
		}
		return "";
	}

	private static boolean judgmentHeader(XSSFRow headerRow, Map<String, String> headerTitleMap) {
		boolean flag = true;
		Integer count = 0;
		for (Map.Entry<String, String> entry : headerTitleMap.entrySet()) {
			if (!getCellValue(headerRow, count).equals(entry.getValue())) {
				flag = false;
				break;
			}
			count += 1;
		}
		return flag;
	}

	private static String getCommaFormat(BigDecimal value) {
		if (value.equals(BigDecimal.ZERO)) {
			return "00.00";
		}
		else {
			return getFormat(",###.00", value);
		}

	}

	private static String getFormat(String style, BigDecimal value) {
		DecimalFormat df = new DecimalFormat();
		df.applyPattern(style);
		return df.format(value.doubleValue());
	}

	public static void setMergedRegion(XSSFSheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
		CellRangeAddress mergeRegion = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
		sheet.addMergedRegion(mergeRegion);
	}

	public static void setRowHeight(XSSFRow row) {
		setRowHeight(row, 25);
	}

	public static void setRowHeight(XSSFRow row, int height) {
		row.setHeight((short) (height * 20));
	}

	/**
	 * 创建公共样式并设置
	 * @return 结果
	 */
	public static XSSFCellStyle createCellStyle(XSSFWorkbook wb) {
		return createCellStyle(wb, HorizontalAlignment.CENTER);
	}

	/**
	 * 创建公共样式并设置
	 * @return 结果
	 */
	public static XSSFCellStyle createCellStyle(XSSFWorkbook wb, HorizontalAlignment alignment) {
		cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(alignment);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		return cellStyle;
	}

	/**
	 * 单元格填充内容，默认10
	 * @param wb
	 * @param cell
	 * @param value
	 * @return 结果
	 */
	public static Cell setCellStyleWithValue(XSSFWorkbook wb, Cell cell, String value) {
		return setCellStyleWithValue(wb, cell, 10, value);
	}

	/**
	 * 单元格指定样式填充内容，默认10
	 * @param wb
	 * @param cell
	 * @param style
	 * @param value
	 * @return 结果
	 */
	public static Cell setCellStyleWithValue(XSSFWorkbook wb, Cell cell, XSSFCellStyle style, String value) {
		return setCellStyleWithValue(wb, cell, style, 10, value);
	}

	/**
	 * 单元格指定文字大小填充内容，默认公共样式
	 * @param wb
	 * @param cell
	 * @param fontSize
	 * @param value
	 * @return 结果
	 */
	public static Cell setCellStyleWithValue(XSSFWorkbook wb, Cell cell, int fontSize, String value) {
		return setCellStyleWithValue(wb, cell, createCellStyle(wb), fontSize, value);
	}

	/**
	 * 单元格填充内容，并设置大小,指定样式
	 * @param wb
	 * @param cell
	 * @param style
	 * @param fontSize
	 * @param value
	 * @return 结果
	 */
	public static Cell setCellStyleWithValue(XSSFWorkbook wb, Cell cell, XSSFCellStyle style, int fontSize,
			String value) {
		cell.setCellValue(value);
		XSSFFont font = wb.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) fontSize);
		style.setFont(font);
		cell.setCellStyle(style);
		return cell;
	}

}
